Combining INDEX and MATCH Functions in Microsoft Excel

Course: Advanced Excel Training

Topic: Combining INDEX and MATCH Functions

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to INDEX and MATCH Functions

Microsoft Excel provides several lookup functions that help users retrieve information from large datasets. Two important functions are INDEX and MATCH. When combined, they create a flexible lookup solution for retrieving values based on a specific search criterion.

The MATCH function identifies the relative position of a value in a row or column, while the INDEX function returns the value located at a specified position within a range. Together, they can retrieve employee salaries, product prices, student marks, order details, inventory quantities, and other business information.

Unlike a traditional VLOOKUP formula, the INDEX and MATCH combination can return values from columns located either to the left or to the right of the lookup column. It can also support two-way lookups that identify a value using both a row label and a column heading.

For official documentation, refer to Microsoft's guide: Look up values with VLOOKUP, INDEX, or MATCH.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand the purpose and syntax of INDEX and MATCH.
• Use MATCH to find the position of a lookup value.
• Use INDEX to return a value from a specified range.
• Combine INDEX and MATCH for exact-match lookups.
• Retrieve values from columns to the left of the lookup column.
• Create two-way lookups using row and column MATCH functions.
• Handle common errors such as #N/A and #REF!.
• Apply INDEX and MATCH to practical business datasets.

3. What Is the INDEX Function?

The INDEX function returns a value or reference from a specified position within a range or array. You provide the range and the row number, and optionally the column number, to identify the value you need.

Syntax

=INDEX(array, row_num, [column_num])

Example of INDEX

Suppose cells A2:A5 contain the following product names:

To return the third value in the range A2:A5, use:

=INDEX(A2:A5,3)

Result: Mouse

Excel returns the third item in the selected range. The row number is relative to the range A2:A5, so the third item is in worksheet cell A4.

4. What Is the MATCH Function?

The MATCH function searches for a specified value within a single row or column and returns its relative position. It does not return the matched value itself; it returns the position where the value is found.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

Understanding MATCH Types

For most business lookups involving product IDs, employee names, order numbers, and customer codes, use 0 to request an exact match.

Example of MATCH

Suppose cells A2:A5 contain Laptop, Keyboard, Mouse, and Monitor. To find the position of Mouse, enter:

=MATCH("Mouse",A2:A5,0)

Result: 3

Mouse is the third item in the range A2:A5. MATCH returns the relative position 3 rather than the worksheet row number 4.

5. Why Combine INDEX and MATCH?

INDEX and MATCH are more useful together because one function locates the position and the other retrieves the corresponding value. This approach provides flexibility when worksheet layouts change or when the lookup column is not the first column in the dataset.

6. Basic INDEX and MATCH Formula

The most common combination uses MATCH inside INDEX:

=INDEX(return_range,MATCH(lookup_value,lookup_range,0))

Here is how the formula works:

1. lookup_value: The value you want to find.
2. lookup_range: The range containing the value you want to match.
3. MATCH: Searches for the value and returns its relative position.
4. INDEX: Uses that position to retrieve the corresponding value from the return range.

Both ranges should cover the same number of rows when performing a standard vertical lookup. The exact-match argument 0 helps prevent an unintended approximate match.

7. Practical Example: Finding a Product Price

Imagine that a company maintains the following product list:

Assume the data is arranged in A2:D6. Cell F2 contains the product ID to search for, such as P103. To return the product price, enter this formula in G2:

=INDEX(D2:D6,MATCH(F2,A2:A6,0))

Result: 650

Explanation:

• MATCH searches for the value in F2 within A2:A6.
• P103 is the third item in that range, so MATCH returns 3.
• INDEX retrieves the third item from D2:D6.
• The returned price is 650.

8. Retrieving Employee Information

INDEX and MATCH can retrieve employee information such as department, salary, job title, or joining date based on an employee ID.

Assume Employee ID is in A2:A5, Department is in C2:C5, and Salary is in D2:D5. Cell F2 contains the employee ID.

To retrieve the employee's department:

=INDEX(C2:C5,MATCH(F2,A2:A5,0))

To retrieve the employee's salary:

=INDEX(D2:D5,MATCH(F2,A2:A5,0))

If F2 contains E203, the formulas return IT and 50000, respectively.

9. Looking Up Values from a Column on the Left

A useful advantage of INDEX and MATCH is that the return column does not need to be to the right of the lookup column. This is helpful when an existing spreadsheet has columns in an order that cannot easily be changed.

Suppose the worksheet has the following layout:

To find the employee name using an ID entered in E2, use:

=INDEX(A2:A4,MATCH(E2,C2:C4,0))

If E2 contains E203, the result is Amit. MATCH searches the Employee ID column, and INDEX returns the value from the corresponding position in the Employee Name column.

10. Two-Way Lookup Using INDEX and MATCH

A two-way lookup retrieves a value based on both a row criterion and a column heading. For example, a sales report may contain employee names in rows and months in columns. You can identify a particular employee and month to retrieve the sales value at their intersection.

Consider this dataset:

Assume the table occupies A1:D4. Cell F2 contains an employee name, such as Priya, and cell G2 contains a month, such as February.

Use this formula to return the sales amount:

=INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G2,B1:D1,0))

Result: 35000

Formula explanation:

• MATCH(F2,A2:A4,0) finds Priya's relative row position, which is 2.
• MATCH(G2,B1:D1,0) finds February's relative column position, which is 2.
• INDEX(B2:D4,2,2) returns the value at the intersection of the second row and second column of B2:D4.

This technique is useful for monthly sales reports, employee performance dashboards, financial summaries, and departmental analysis.

11. Using Cell References for Dynamic Lookups

A dynamic lookup allows users to change the search value without editing the formula. Instead of typing a fixed product ID or employee name into the formula, place the lookup value in a separate cell.

=INDEX($D$2:$D$100,MATCH(F2,$A$2:$A$100,0))

In this example, F2 contains the value to search for, A2:A100 contains the lookup IDs, and D2:D100 contains the return values. The dollar signs create absolute references so that the ranges remain fixed when the formula is copied.

When F2 changes from P101 to P104, the result updates automatically to the corresponding product price.

12. Using IFERROR with INDEX and MATCH

If MATCH cannot find the requested value, Excel typically returns #N/A. The IFERROR function can replace that error with a more understandable message.

=IFERROR(INDEX(D2:D6,MATCH(F2,A2:A6,0)),"Product not found")

If the product ID in F2 exists in A2:A6, the formula returns its price. If the ID is missing, it displays “Product not found.”

Use a meaningful error message when the spreadsheet will be used by other employees or customers. However, do not use IFERROR to hide errors that should be investigated during formula development.

13. Combining INDEX and MATCH with Multiple Criteria

Sometimes one lookup value is not sufficient. For example, a business may need to find a price using both a product name and a supplier. In such cases, the formula must check both criteria.

Assume the following data is stored in A2:C5:

Suppose E2 contains the product name and F2 contains the supplier. In modern Excel versions, use:

=INDEX(C2:C5,MATCH(1,(A2:A5=E2)*(B2:B5=F2),0))

This formula evaluates both conditions. The multiplication of the logical tests produces 1 where both conditions are true. MATCH locates that row, and INDEX returns the corresponding price.

In Microsoft 365 and other versions with dynamic array calculation, this formula can generally be entered normally. In some older Excel versions, array formulas may require Ctrl+Shift+Enter. Check compatibility with the Excel version being used.

14. Approximate Match Considerations

Although exact matches are the most common use of INDEX and MATCH for IDs and names, MATCH also supports approximate matching. For example, it can help identify a commission rate or grade based on a numeric threshold.

=MATCH(E2,A2:A6,1)

When the match type is 1, the lookup range must be sorted in ascending order. MATCH returns the position of the largest value that is less than or equal to the lookup value. If the data is not sorted correctly, the result may be incorrect.

For a standard lookup involving unique product codes or employee IDs, prefer an exact match with 0.

15. Common Errors and Troubleshooting

16. Best Practices for INDEX and MATCH

• Use MATCH(...,0) for exact-match lookups involving names, codes, and IDs.
• Ensure lookup and return ranges have matching dimensions and aligned records.
• Use absolute references when copying formulas across cells.
• Use unique identifiers wherever possible to avoid ambiguous matches.
• Keep column headers and source data consistent.
• Use Excel Tables and structured references for datasets that grow over time.
• Use IFERROR when a user-friendly fallback message is appropriate.
• Test formulas with known values, missing values, and duplicate records.
• Document complex formulas so that other users can maintain the workbook.
• Consider XLOOKUP or XMATCH in newer Excel versions when their features better suit the task.

17. INDEX and MATCH Compared with VLOOKUP

INDEX and MATCH are particularly useful when a workbook's column order is fixed or when the lookup value is not located in the leftmost column of the dataset.

18. Practical Business Applications

Sales Management

Find the sales amount for a specific employee and month, or retrieve customer information using a customer ID.

Inventory Management

Retrieve a product's price, stock quantity, category, or supplier details using its product code.

Human Resources

Find employee salaries, departments, job titles, or joining dates using unique employee identifiers.

Finance and Accounting

Retrieve invoice details, payment dates, account codes, or budget amounts from financial records.

Education

Find student marks, grades, or attendance information based on a student ID and subject name.

19. Practice Exercises

Use the following sample dataset to practise combining INDEX and MATCH.

1. Write a formula to return the student name for student ID S103.
2. Write a formula to retrieve the marks of student ID S102.
3. Find the course associated with student ID S104.
4. Use IFERROR to display “Student not found” when an ID does not exist.
5. Create a two-way lookup using students as row labels and subjects as column headers in a separate marks matrix.

20. Mini Project: Product Lookup Worksheet

Create a small product lookup tool using the following steps:

1. Create a product table with Product ID, Product Name, Category, Supplier, and Price columns.
2. Reserve a separate cell for entering the Product ID to search for.
3. Use INDEX and MATCH to retrieve the product name.
4. Add formulas to retrieve category, supplier, and price.
5. Use IFERROR to display a helpful message when a Product ID is not found.
6. Format the worksheet with clear headings, appropriate number formats, and readable labels.
7. Test the worksheet with valid IDs, missing IDs, and repeated product names.

This project demonstrates how lookup formulas can turn a static product list into a practical search tool.

21. Interview Questions and Answers

Q1. What is the purpose of the INDEX function?

Answer: INDEX returns a value or reference from a range based on a specified row position and, when applicable, a column position.

Q2. What does MATCH return?

Answer: MATCH returns the relative position of a lookup value within a single row or column.

Q3. Why are INDEX and MATCH combined?

Answer: MATCH locates the position of a value, while INDEX retrieves the corresponding result from a specified range.

Q4. What does the 0 in MATCH mean?

Answer: It requests an exact match.

Q5. Can INDEX and MATCH look to the left?

Answer: Yes. The return range can be located to the left or right of the lookup range.

Q6. How can you handle a missing lookup value?

Answer: Use IFERROR to return a helpful message, such as “Record not found,” when an error occurs.

Q7. What is a two-way lookup?

Answer: A two-way lookup returns the value at the intersection of a matched row and a matched column. It commonly uses INDEX with two MATCH functions.

Q8. What causes #N/A in a MATCH formula?

Answer: The lookup value may not exist in the lookup range, or there may be differences such as extra spaces or mismatched data types.

Q9. What is the difference between INDEX and MATCH?

Answer: INDEX retrieves a value from a position, while MATCH finds the position of a specified value.

Q10. Is INDEX and MATCH useful in older Excel versions?

Answer: Yes. Both functions are supported in many older Excel versions and are useful when newer functions such as XLOOKUP are unavailable.

22. Quick Revision Checklist

• INDEX returns a value from a specified position.
• MATCH returns a relative position within a lookup range.
• The standard exact-match formula is =INDEX(return_range,MATCH(lookup_value,lookup_range,0)).
• Use 0 in MATCH for exact matches.
• INDEX and MATCH can return values from columns to the left of the lookup range.
• Two-way lookups use one MATCH for the row and another for the column.
• IFERROR can provide a readable message when a lookup fails.
• Lookup ranges should be aligned and carefully checked.
• Unique identifiers help avoid ambiguous results.

23. Conclusion

Combining INDEX and MATCH is an important advanced Excel skill for retrieving information from structured datasets. MATCH finds the position of a value, and INDEX uses that position to return the corresponding result. Together, these functions support exact lookups, left-side lookups, two-way searches, and flexible reporting solutions.

Practise the formulas with employee records, product lists, sales reports, and student datasets to develop confidence in using them for real-world business tasks.

To explore more advanced Excel topics, visit the Advanced Excel Training course, register through the course demo registration page, or explore the JustAcademy website.

Reference

Microsoft Support: Look up values with VLOOKUP, INDEX, or MATCH.